package com.hellobike.haxing.modules.statics.mapper;

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.hellobike.haxing.modules.product.entity.Product;
import com.hellobike.haxing.modules.statics.dto.*;
import com.hellobike.haxing.modules.statics.param.CategorySalesParam;
import com.hellobike.haxing.modules.statics.param.OrderDetailParam;
import com.hellobike.haxing.modules.statics.param.ShopSalesStaticsParam;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface OrderDetailStaticsMapper {
    @Select("<script>" +
            " SELECT ts.address, ts.shop_name,a.create_time ,sp.order_spec_unit, ts.mobile, ts.real_name,a.status as statusInteger, oa.m_phone\n" +
            "\t, oa.receiver, b.cogs_price*b.num as cogs_price,b.leader_amount\n" +
            "\t, CASE \n" +
            "\t\tWHEN a.status = 2 THEN '取消'\n" +
            "\t\tWHEN a.status = 3 THEN '待取货'\n" +
            "\t\tWHEN a.status = 5 THEN '已取货'\n" +
            "\t\tWHEN a.status = 6 THEN '已完结'\n" +
            "\t\tWHEN a.status = 10 THEN '售后成功'\n" +
            "\t\tWHEN a.status = 14 THEN '售后失败'\n" +
            "\t\tWHEN a.status = 15 THEN '售后成功'\n" +
            "\tEND AS status, a.pay_time, a.bill_no, b.title, b.num\n" +
            "\t, b.commission_rate,  b.product_amount, CASE WHEN toad2.id >0 then cast(b.shop_amount*(b.product_amount-toad2.after_amount)/b.product_amount as  decimal(10,2)) else b.shop_amount end as shop_amount,  b.coupon_amount, b.product_amount-b.coupon_amount as pay_amount\n" +
            "\t, s.barcode , a.shop_id, s.id as sku_id\n" +
            "\t, s.spu_id" +
            "," +
            "case\n" +
            "\t\twhen sp.store_type  = 1 then coalesce(tppr.supplier_name ,(select supplier_name from t_supplier_cl_pro tscp where tscp.sku_id = b.sku_id order by tscp.is_default limit 1) )\n" +
            "\t\telse '好想你健康食品股份有限公司'\n" +
            "\tend as supplier_name\n" +
            "FROM t_order a\n" +
            "\tJOIN t_order_detail b\n" +
            "\tON a.id = b.order_id\n" +
            "\t\tAND a.success_time between #{orderDetailParam.start} and  #{orderDetailParam.end}\n" +
            "\tJOIN t_order_address oa ON oa.order_id = a.id\n" +
            "\tJOIN t_sku s ON s.id = b.sku_id\n" +
            "\tJOIN t_spu sp ON sp.id = s.spu_id\n" +
            "\tJOIN t_shop ts ON ts.id = a.shop_id\n  " +
            "LEFT JOIN t_order_aftersale toad ON toad.order_id=a.id AND toad.after_status=15 \n" +
            "LEFT JOIN t_order_aftersale_detail toad2 ON toad2.order_id=a.id AND toad2.order_detail_id=b.id AND toad.id = toad2.order_after_id \n" +
            "\t<if test=\"orderDetailParam.mobile != null and orderDetailParam.mobile != ''\">\n" +
            "\t  AND ts.mobile=#{orderDetailParam.mobile}\n" +
            "\t</if>\n" +
            "\tLEFT JOIN  t_purchase_pro_record tppr on tppr.sku_id = b.sku_id  and  tppr.order_code= a.bill_no \n" +
            "\t<if test=\"orderDetailParam.supplierId != null \">\n" +
            "\t  where tmp_tscp.supplier_id=#{orderDetailParam.supplierId}\n" +
            "\t</if>\n" +
            "\t order by a.id desc </script>")
    IPage<OrderDetailStatics> selectPage(IPage<OrderDetailStatics> page, OrderDetailParam orderDetailParam);

    @Select("SELECT c.name, c.count AS product_count\n" +
            "\t, round(100.0 * count / SUM(count) OVER (), 2)\n" +
            "\t|| '%' AS perc_count\n" +
            "\t, productAmount AS product_amount\n" +
            "\t, round(100.0 * productAmount / SUM(productAmount) OVER (), 2)\n" +
            "\t|| '%' AS perc_amount\n" +
            "FROM (\n" +
            "\tSELECT c.name, SUM(b.num) AS count, SUM(b.product_amount) AS productAmount\n" +
            "\tFROM t_order a\n" +
            "\t\tJOIN t_order_detail b ON a.id = b.order_id\n" +
            "\t\tJOIN t_sku ss ON ss.id = b.sku_id\n" +
            "\t\tJOIN t_product p ON p.id = ss.spu_id\n" +
            "\t\tJOIN t_category c ON c.id = p.category_id1\n" +
            "\tWHERE a.success_time BETWEEN #{categorySalesParam.start} and  #{categorySalesParam.end}\n" +
            "\tGROUP BY 1\n" +
            ") c")
    IPage<CategorySalesStatics> selectCategoryPage(IPage<CategorySalesStatics> page, CategorySalesParam categorySalesParam);

    @Select("<script>" +
            "SELECT b.success_time,a.shop_name, d.name AS city_name, e.name AS distict_name, f.name AS street_name, a.real_name, a.mobile\n" +
            "\t, SUM(c.num) AS product_count, SUM(case when b.status != 2 then c.shop_amount else 0 end ) AS shop_amount,\n" +
            "\tCOUNT(DISTINCT CASE \n" +
            "\t\tWHEN b.status != 2 THEN b.bill_no\n" +
            "\t\tELSE NULL\n" +
            "\tEND) AS pay_count, SUM(CASE \n" +
            "\t\tWHEN b.status !=2 THEN c.product_amount - c.coupon_amount\n" +
            "\t\tELSE 0\n" +
            "\tEND) AS pay_amount,\n" +
            "\tCOUNT(DISTINCT CASE \n" +
            "\t\tWHEN b.status = 2 THEN b.bill_no\n" +
            "\t\tELSE NULL\n" +
            "\tEND) AS cancel_count, SUM(CASE \n" +
            "\t\tWHEN b.status = 2 THEN c.product_amount - c.coupon_amount\n" +
            "\t\tELSE 0\n" +
            "\tEND) AS cancel_amount\n" +
            "FROM t_shop a\n" +
            "\tJOIN t_order b ON a.id = b.shop_id\n" +
            "\tJOIN t_order_detail c ON c.order_id = b.id\n" +
            "\tJOIN t_city d ON d.id = a.city_id\n" +
            "\tJOIN t_district e ON e.id = a.district_id\n" +
            "\tLEFT JOIN t_street f ON f.id = a.street_id\n" +
            "WHERE b.success_time between #{shopSalesStaticsParam.start} and #{shopSalesStaticsParam.end}\n" +
            "<if test=\"shopSalesStaticsParam.mobile != null and shopSalesStaticsParam.mobile != ''\">\n" +
            "   and a.mobile = #{shopSalesStaticsParam.mobile}\n" +
            "</if>\n" +
            "<if test=\"shopSalesStaticsParam.realName != null and shopSalesStaticsParam.realName != ''\">\n" +
            "   and a.real_name like concat('%', #{shopSalesStaticsParam.realName},'%')\n" +
            "</if>\n" +
            "group by b.success_time,a.shop_name, d.name , e.name, f.name, a.real_name, a.mobile" +
            "</script>")
    IPage<ShopSalesStatics> selectShopSalesStatis(IPage<ShopSalesStatics> page, ShopSalesStaticsParam shopSalesStaticsParam);

    @Select("select * from (SELECT p.title AS count_title,s.sku_code, SUM(b.num) AS product_count\n" +
            "FROM t_order a\n" +
            "\tJOIN t_order_detail b ON a.id = b.order_id and a.status in (3,4,5,6)\n" +
            "\t JOIN  t_sku s ON s.id = b.sku_id \n" +
            "\tJOIN t_product p\n" +
            "\tON p.id = b.product_id\n" +
            "\t\tAND a.success_time BETWEEN #{orderDetailParam.start} AND #{orderDetailParam.end}\n" +
            "GROUP BY p.title, s.sku_code\n" +
            "ORDER BY SUM(b.num) DESC\n" +
            "LIMIT 300) n")
    IPage<ProductStats> selectProductCountStats(IPage<ProductStats> page, OrderDetailParam orderDetailParam);

    @Select("select * from (SELECT p.title AS amount_title,s.sku_code, SUM(b.product_amount) AS product_amount\n" +
            "FROM t_order a\n" +
            "\tJOIN t_order_detail b ON a.id = b.order_id and a.status in (3,4,5,6)\n" +
            "\t JOIN  t_sku s ON s.id = b.sku_id \n" +
            "\tJOIN t_product p\n" +
            "\tON p.id = b.product_id\n" +
            "\t\tAND a.success_time BETWEEN #{orderDetailParam.start} AND #{orderDetailParam.end}\n" +
            "GROUP BY p.title,s.sku_code\n" +
            "ORDER BY SUM(b.product_amount) DESC\n" +
            "LIMIT 300) n")
    IPage<ProductStats> selectProductAmountStats(IPage<ProductStats> page, OrderDetailParam orderDetailParam);

    @Select("SELECT *\n" +
            "FROM (\n" +
            "\t\tSELECT to_char(create_time, 'yyyy-MM-dd') as date,count(1) as order_count, SUM(CASE \n" +
            "\t\t\tWHEN is_pay = 1 THEN 1\n" +
            "\t\t\tELSE 0\n" +
            "\t\tEND) AS pay_count,\n" +
            "\tround(SUM(CASE \n" +
            "\t\t\tWHEN is_pay = 1 THEN 1\n" +
            "\t\t\tELSE 0\n" +
            "\t\tEND)*100.0/count(1),2) || '%' as pay_rate,\n" +
            "\tSUM(CASE \n" +
            "\t\t\tWHEN is_pay = 1 and status != 2 THEN 1\n" +
            "\t\t\tELSE 0\n" +
            "\t\tEND) AS valid_count,\n" +
            "\tround(SUM(CASE \n" +
            "\t\t\tWHEN is_pay = 1 and status != 2 THEN 1\n" +
            "\t\t\tELSE 0\n" +
            "\t\tEND)*100.0/SUM(CASE \n" +
            "\t\t\tWHEN is_pay = 1 THEN 1\n" +
            "\t\t\tELSE 0.0000000001\n" +
            "\t\tEND),2) || '%' as valid_rate\n" +
            "\tFROM t_order a \n" +
            "WHERE a.create_time BETWEEN #{orderDetailParam.start} AND #{orderDetailParam.end}\n" +
            "\tGROUP BY 1\n" +
            "\tORDER BY 1 ASC\n" +
            ") n")
    IPage<OrderPayStats> selectOrderPay(IPage<OrderPayStats> page, OrderDetailParam orderDetailParam);
}
